using Dapper;
using Lzfy_His_Service.Models;
using Lzfy_His_Service.Services;

namespace Lzfy_His_Service.Services
{
    public class HisPatientBalanceService : IHisPatientBalanceService
    {
        private readonly IOracleConnectionFactory _oracleConnectionFactory;
        private readonly ILogger<HisPatientBalanceService> _logger;

        public HisPatientBalanceService(IOracleConnectionFactory oracleConnectionFactory, ILogger<HisPatientBalanceService> logger)
        {
            _oracleConnectionFactory = oracleConnectionFactory;
            _logger = logger;
        }

        public async Task<decimal?> GetPatientBalanceAsync(string patientId)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(patientId))
                {
                    _logger.LogWarning("患者ID不能为空");
                    return null;
                }

                using var connection = _oracleConnectionFactory.CreateConnection();
                connection.Open();
                
                var sql = @"
                    select t1.balance 
                    from pca.pca_prepay_account_master t1 
                    join pca.pca_prepay_account_index t2 
                      on t1.account_index = t2.account_index 
                     and t1.account_type = 'O' 
                     and t2.patient_id = :patientId";

                var balance = await connection.QueryFirstOrDefaultAsync<decimal?>(sql, new { patientId });
                
                _logger.LogInformation($"查询患者余额: {patientId}, 余额: {balance}");
                
                return balance;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"查询患者余额失败: {patientId}");
                throw;
            }
        }

        public async Task<string?> GetPatientQrCodeAsync(string patientId)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(patientId))
                {
                    _logger.LogWarning("患者ID不能为空");
                    return null;
                }

                using var connection = _oracleConnectionFactory.CreateConnection();
                connection.Open();
                
                // 第一步：查询 pca_patient_info 表
                var primarySql = "select qr_code as qrCode from pca.pca_patient_info where patient_id = :patientId";
                var qrCode = await connection.QueryFirstOrDefaultAsync<string>(primarySql, new { patientId });
                
                // 如果查询结果不为空且不为空字符串，直接返回
                if (!string.IsNullOrEmpty(qrCode))
                {
                    _logger.LogInformation($"从pca_patient_info查询到qrCode: {patientId}");
                    return qrCode;
                }
                
                // 第二步：根据patientId开头字母进行不同查询
                if (patientId.StartsWith("9"))
                {
                    // 9开头的patientId查询逻辑
                    
                    var sql9 = @"
                        select ';' || t1.track_data || '?' as qrCode 
                        from pca.pca_patient_service_card_info t1, pca.pca_media_info t2 
                        where t1.card_no = t2.identity_number 
                          and t2.patient_id = :patientId
                        and rownum = 1";
                        
                    
                    qrCode = await connection.QueryFirstOrDefaultAsync<string>(sql9, new { patientId });
                    _logger.LogInformation($"从pca_patient_service_card_info查询到qrCode: {patientId}, 结果: {qrCode}");
                }
                else if (patientId.StartsWith("8"))
                {
                    // 8开头的patientId查询逻辑
                    /*var sql8 = @"
                        select ';' || t1.patient_id || '|' || t1.media_id || '?' as qrCode 
                        from PCA.PCA_MEDIA_INFO t1 
                        where t1.media_id in 
                              (select t2.media_id 
                               from PCA.PCA_MEDIA_VERIFY_INFO t2 
                               where t2.identity_number = :patientId)
                        and rownum = 1";
                        */
                    var sql8 = @"select ';' || t1.media_uuid || '?' as qrCode
                        from PCA.PCA_MEDIA_VERIFY_INFO t1
                        where t1.identity_number = :patientId
                        and rownum = 1";
                    qrCode = await connection.QueryFirstOrDefaultAsync<string>(sql8, new { patientId });
                    _logger.LogInformation($"从PCA_MEDIA_INFO查询到qrCode: {patientId}, 结果: {qrCode}");
                }
                else
                {
                    _logger.LogWarning($"患者ID {patientId} 不以8或9开头，无法进行二次查询");
                }
                
                return qrCode;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"查询患者qrCode失败: {patientId}");
                throw;
            }
        }

        public async Task<string?> GetRefundBalanceSnAsync(string patientId, decimal hisBalance)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(patientId))
                {
                    _logger.LogWarning("患者ID不能为空");
                    return null;
                }

                using var connection = _oracleConnectionFactory.CreateConnection();
                connection.Open();
                
                var sql = @"
                    select t1.balance_sn as refund_balance_sn 
                    from pca.pca_prepay_balance t1 
                    join pca.pca_prepay_account_master t2 
                      on t1.account_id = t2.account_id 
                     and t2.account_type = 'O' 
                     and t1.in_or_out = '2' 
                     and t1.balance_amount = 0 
                     and t1.trans_type_code = '05' 
                     and t1.trans_time > sysdate - 1 / 72 
                    join pca.pca_prepay_account_index t3 
                      on t2.account_index = t3.account_index 
                   where t3.patient_id = :patientId 
                     and t1.amount = :hisBalance 
                     and rownum = 1 
                   order by t1.trans_time desc";

                var refundBalanceSn = await connection.QueryFirstOrDefaultAsync<string>(sql, new { patientId, hisBalance });
                
                _logger.LogInformation($"查询患者退费流水号: {patientId}, HIS余额: {hisBalance}, 流水号: {refundBalanceSn}");
                
                return refundBalanceSn;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"查询患者退费流水号失败: {patientId}, HIS余额: {hisBalance}");
                throw;
            }
        }

        public async Task<List<HisRefundData>> GetHisRefundDataAsync(string patientId, decimal amount, int days)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(patientId))
                {
                    _logger.LogWarning("患者ID不能为空");
                    return new List<HisRefundData>();
                }

                using var connection = _oracleConnectionFactory.CreateConnection();
                
                var sql = @"
                    select t4.name as Name, 
                           decode(t4.sex_code, '1', '男', '2', '女', '未知') as Sex, 
                           t4.patient_id as PatientId, 
                           t4.id_number as IdNumber, 
                           t4.phone_number1 as PhoneNumber1, 
                           t1.balance_sn as BalanceSn, 
                           t1.amount as Amount, 
                           t1.trans_time as TransTime 
                    from pca.pca_prepay_balance t1 
                    join pca.pca_prepay_account_master t2 
                      on t1.account_id = t2.account_id 
                     and t2.account_type = 'O' 
                     and t1.in_or_out = '2' 
                     and t1.balance_amount = 0 
                     and t1.trans_type_code = '05' 
                    join pca.pca_prepay_account_index t3 
                      on t2.account_index = t3.account_index 
                    join pca.pca_patient_info t4 
                      on t3.patient_id = t4.patient_id 
                   where t3.patient_id = :patientId 
                     and t1.amount = :amount 
                     and t1.trans_time > sysdate - :days 
                   order by t1.trans_time desc";

                // 输出完整的SQL语句用于调试
                var debugSql = sql.Replace(":patientId", $"'{patientId}'").Replace(":amount", amount.ToString()).Replace(":days", days.ToString());
                _logger.LogError($"执行的SQL语句: {debugSql}");

                var result = await connection.QueryAsync<HisRefundData>(sql, new { patientId, amount, days });
                
                _logger.LogInformation($"查询HIS退费数据: {patientId}, 金额: {amount}, 天数: {days}, 结果数量: {result.Count()}");
                
                return result.ToList();
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"查询HIS退费数据失败: {patientId}, 金额: {amount}, 天数: {days}");
                throw;
            }
        }

        public async Task<int> GetPatientRechargeCountAsync(string patientId)
        {
            try
            {
                if (string.IsNullOrWhiteSpace(patientId))
                {
                    _logger.LogWarning("患者ID不能为空");
                    return 0;
                }

                using var connection = _oracleConnectionFactory.CreateConnection();
                connection.Open();
                
                var sql = @"
                    select count(t3.in_or_out) as incomingCount 
                    from pca.pca_prepay_account_master t1 
                    join pca.pca_prepay_account_index t2 
                      on t1.account_index = t2.account_index 
                     and t1.account_type = 'O' 
                    join pca.pca_prepay_balance t3 
                      on t1.account_id = t3.account_id 
                     and t3.in_or_out = '1' 
                   where t2.patient_id = :patientId 
                     and t3.trans_time > to_date('2025-03-26','yyyy-mm-dd')";

                var count = await connection.QueryFirstOrDefaultAsync<int>(sql, new { patientId });
                
                _logger.LogInformation($"查询患者充值记录数量: {patientId}, 从3月26日之后, 数量: {count}");
                
                return count;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, $"查询患者充值记录数量失败: {patientId}, 从3月26日之后");
                throw;
            }
        }
    }
}